<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.2">
  <link rel="apple-touch-icon" sizes="180x180" href="/blog/images/icon.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/blog/images/icon.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/blog/images/icon.png">
  <link rel="mask-icon" href="/blog/images/icon.svg" color="#222">

<link rel="stylesheet" href="/blog/css/main.css">


<link rel="stylesheet" href="/blog/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"bgape002.gitee.io","root":"/blog/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":true,"show_result":true,"style":"mac"},"back2top":{"enable":true,"sidebar":false,"scrollpercent":true},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="数据库基础概念 什么是数据库？  DB（database），按照一定格式存储数据的一些文件的组合。   数据库管理系统  DBMS（database management system）,专门用来管理数据库中数据的，可以对数据库当中的数据进行增删改查。 常见的数据库管理系统：MySQL、Oracle、MS SqlServer、DB2、sybase等….   SQL：结构化查询语言  程序员需要学习">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql review">
<meta property="og:url" content="https://bgape002.gitee.io/2021/09/09/mysql-review/index.html">
<meta property="og:site_name" content="bgape002">
<meta property="og:description" content="数据库基础概念 什么是数据库？  DB（database），按照一定格式存储数据的一些文件的组合。   数据库管理系统  DBMS（database management system）,专门用来管理数据库中数据的，可以对数据库当中的数据进行增删改查。 常见的数据库管理系统：MySQL、Oracle、MS SqlServer、DB2、sybase等….   SQL：结构化查询语言  程序员需要学习">
<meta property="og:locale" content="zh_CN">
<meta property="article:published_time" content="2021-09-09T12:04:25.000Z">
<meta property="article:modified_time" content="2022-01-27T02:16:32.325Z">
<meta property="article:author" content="bgape002">
<meta property="article:tag" content="mysql">
<meta name="twitter:card" content="summary">

<link rel="canonical" href="https://bgape002.gitee.io/2021/09/09/mysql-review/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>mysql review | bgape002</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/blog/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">bgape002</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">淡泊明志，宁静致远</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/blog/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/blog/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签<span class="badge">66</span></a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/blog/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类<span class="badge">27</span></a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/blog/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档<span class="badge">61</span></a>

  </li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup">
        <div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div id="search-result">
  <div id="no-result">
    <i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
  </div>
</div>

    </div>
  </div>

</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://bgape002.gitee.io/2021/09/09/mysql-review/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/blog/images/head.png">
      <meta itemprop="name" content="bgape002">
      <meta itemprop="description" content="mail: bgape002@163.com">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="bgape002">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          mysql review
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2021-09-09 20:04:25" itemprop="dateCreated datePublished" datetime="2021-09-09T20:04:25+08:00">2021-09-09</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">更新于</span>
                <time title="修改时间：2022-01-27 10:16:32" itemprop="dateModified" datetime="2022-01-27T10:16:32+08:00">2022-01-27</time>
              </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/blog/categories/Databases/" itemprop="url" rel="index"><span itemprop="name">Databases</span></a>
                </span>
            </span>

          
            <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv" style="display: none;">
              <span class="post-meta-item-icon">
                <i class="fa fa-eye"></i>
              </span>
              <span class="post-meta-item-text">阅读次数：</span>
              <span id="busuanzi_value_page_pv"></span>
            </span>
  
  <span class="post-meta-item">
    
      <span class="post-meta-item-icon">
        <i class="far fa-comment"></i>
      </span>
      <span class="post-meta-item-text">Valine：</span>
    
    <a title="valine" href="/blog/2021/09/09/mysql-review/#valine-comments" itemprop="discussionUrl">
      <span class="post-comments-count valine-comment-count" data-xid="/blog/2021/09/09/mysql-review/" itemprop="commentCount"></span>
    </a>
  </span>
  
  

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h3 id="数据库基础概念"><a href="#数据库基础概念" class="headerlink" title="数据库基础概念"></a>数据库基础概念</h3><ul>
<li><p>什么是数据库？</p>
<blockquote>
<p>DB（database），按照一定格式存储数据的一些文件的组合。</p>
</blockquote>
</li>
<li><p>数据库管理系统</p>
<blockquote>
<p>DBMS（database management system）,专门用来管理数据库中数据的，可以对数据库当中的数据进行增删改查。</p>
<p>常见的数据库管理系统：MySQL、Oracle、MS SqlServer、DB2、sybase等….</p>
</blockquote>
</li>
<li><p>SQL：结构化查询语言</p>
<blockquote>
<p>程序员需要学习SQL语句，程序员通过编写SQL语句，然后DBMS负责执行SQL<br>语句，最终来完成数据库中数据的增删改查操作。</p>
<p>SQL是一套标准，程序员主要学习的就是SQL语句，这个SQL在mysql中可以使用，同时在Oracle中也可以使用，在DB2中也可以使用。</p>
</blockquote>
</li>
<li><p>三者之间的关系</p>
<blockquote>
<p>DBMS–执行–&gt; SQL –操作–&gt; DB</p>
</blockquote>
</li>
<li><p>在windows操作系统当中，怎么使用命令来启动和关闭mysql服务呢？</p>
<blockquote>
<p>net stop 服务名称;<br>net start 服务名称;</p>
</blockquote>
</li>
<li><p>windows系统下，登陆mysql数据库:<code>mysql -uroot -p******</code>;退出mysql：<code>exit</code></p>
</li>
<li><p>修改密码：<code>set password for 用户名@localhost=password(&#39;新密码&#39;)</code></p>
</li>
<li><p>忘记root密码</p>
<blockquote>
<ul>
<li>关闭正在运行的MySQL服务。</li>
<li>打开DOS窗口，转到mysql\bin目录。</li>
<li>输入mysqld –skip-grant-tables 回车。–skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。</li>
<li>再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），输入mysql回车，如果成功，将出现MySQL提示符 &gt;。</li>
<li>连接权限数据库： use mysql; </li>
<li>改密码：update user set password=password(“新密码”) where user=”root”;</li>
<li>刷新权限（必须步骤）：flush privileges;　</li>
<li>退出mysql  quit；</li>
</ul>
</blockquote>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">--解决mysql中文显示错误问题</span><br><span class="line">mysql --default-character-set=latin1 -uroot -p***</span><br><span class="line">--在登录mysql时输入以下命令，解决对齐问题  </span><br><span class="line">show character set;</span><br><span class="line">--查看所有字符集</span><br><span class="line">show variables like &#x27;character_set%&#x27;;</span><br><span class="line">--查看服务器默认对外处理的字符集</span><br><span class="line">set character_set_client=gbk;</span><br><span class="line">--修改服务器默认对外客户端的字符集为gbk</span><br><span class="line">set character_set_results=gbk;</span><br><span class="line">--修改服务器给定数据的字符集为gbk</span><br><span class="line">set names gbk;</span><br><span class="line">--快捷设置字符集</span><br></pre></td></tr></table></figure></li>
</ul>
<span id="more"></span>

<h3 id="DDL、DML和DCL"><a href="#DDL、DML和DCL" class="headerlink" title="DDL、DML和DCL"></a>DDL、DML和DCL</h3><ul>
<li><p>DDL( data definition language )  <strong>数据定义语言</strong>，用于操作对象和对象的属性 ,这种对象包括数据库本身，以及数据库对象，像：表、视图等等 </p>
<blockquote>
<p>操作语句：</p>
<p><code> 1.CREATE - 创建</code> </p>
<p> <code>2.ALTER - 修改</code></p>
<p> <code>3.DROP - 删</code></p>
<p> <code>4.TRUNCATE - 移除</code></p>
<p> <code>5.COMMENT - 注释</code></p>
<p> <code>6.RENAME - 重命名 </code></p>
</blockquote>
</li>
<li><p>DML( Data Manipulation Language ) <strong>数据操作语言</strong>，SQL中处理数据等操作统称为数据操纵语言 </p>
<blockquote>
<p>操作语句：</p>
<p><code> 1.SELECT - 查询</code> </p>
<p> <code>2.INSERT - 添加</code></p>
<p> <code>3.UPDATE - 更新 </code></p>
<p> <code>4.DELETE - 删除</code></p>
<p> <code>5.CALL - 调用</code></p>
<p> <code>6.EXPLAIN - 解释</code></p>
<p> <code>7.LOCK TABLE - 锁，用于控制并发 </code></p>
</blockquote>
</li>
<li><p>DCL( Data Control Language ) <strong>数据控制语言</strong>，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等 </p>
<blockquote>
<p>操作语句：</p>
<p><code>1.GRANT - 授权 允许对象的创建者给某用户或某组或所有用户(PUBLIC)某些特定的权限。</code></p>
<p><code>2.REVOKE - 收回已经授予的权限</code></p>
<p><code>3.COMMIT - 提交</code></p>
<p><code>4.SAVEPOINT - 保存点</code></p>
<p><code>5.ROLLBACK - 回滚</code></p>
<p><code>6.SET TRANSACTION - 设置当前事务的特性，它对后面的事务没有影响</code></p>
</blockquote>
</li>
<li><p>DQL：数据查询语言（凡是带有select关键字的都是查询语句）</p>
</li>
<li><p>TCL：事务控制语言。包括：事务提交：commit;事务回滚：rollback;</p>
</li>
</ul>
<h3 id="mysql的数据类型"><a href="#mysql的数据类型" class="headerlink" title="mysql的数据类型"></a>mysql的数据类型</h3><p> <strong>各数据类型及字节长度一览表：</strong> </p>
<table>
<thead>
<tr>
<th align="center">数据类型</th>
<th align="center">字节长度</th>
<th>范围或用法</th>
</tr>
</thead>
<tbody><tr>
<td align="center">Bit</td>
<td align="center">1</td>
<td>无符号[0,255]，有符号[-128,127]，备注：BIT和BOOL布尔型都占用1字节</td>
</tr>
<tr>
<td align="center">TinyInt</td>
<td align="center">1</td>
<td>整数[0,255]</td>
</tr>
<tr>
<td align="center">SmallInt</td>
<td align="center">2</td>
<td>无符号[0,65535]，有符号[-32768,32767]</td>
</tr>
<tr>
<td align="center">MediumInt</td>
<td align="center">3</td>
<td>无符号[0,2^24-1]，有符号[-2^23,2^23-1]]</td>
</tr>
<tr>
<td align="center">Int</td>
<td align="center">4</td>
<td>无符号[0,2^32-1]，有符号[-2^31,2^31-1]</td>
</tr>
<tr>
<td align="center">BigInt</td>
<td align="center">8</td>
<td>无符号[0,2^64-1]，有符号[-2^63 ,2^63 -1]</td>
</tr>
<tr>
<td align="center">Float(M,D)</td>
<td align="center">4</td>
<td>单精度浮点数。提醒这里的D是精度，如果D&lt;=24则为默认的FLOAT，如果D&gt;24则会自动被转换为DOUBLE型。</td>
</tr>
<tr>
<td align="center">Double(M,D)</td>
<td align="center">8</td>
<td>双精度浮点。</td>
</tr>
<tr>
<td align="center">Decimal(M,D)</td>
<td align="center">M+1或M+2</td>
<td>未打包的浮点数，用法类似于FLOAT和DOUBLE，如果在ASP中使用到Decimal数据类型，直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。</td>
</tr>
<tr>
<td align="center">Date</td>
<td align="center">3</td>
<td>以YYYY-MM-DD的格式显示，比如：2009-07-19</td>
</tr>
<tr>
<td align="center">Date Time</td>
<td align="center">8</td>
<td>以YYYY-MM-DD HH:MM:SS的格式显示，比如：2009-07-19 11：22：30</td>
</tr>
<tr>
<td align="center">TimeStamp</td>
<td align="center">4</td>
<td>以YYYY-MM-DD的格式显示，比如：2009-07-19</td>
</tr>
<tr>
<td align="center">Time</td>
<td align="center">3</td>
<td>以HH:MM:SS的格式显示。比如：11：22：30</td>
</tr>
<tr>
<td align="center">Year</td>
<td align="center">1</td>
<td>以YYYY的格式显示。比如：2009</td>
</tr>
<tr>
<td align="center">Char(M)</td>
<td align="center">M</td>
<td>定长字符串。</td>
</tr>
<tr>
<td align="center">VarChar(M)</td>
<td align="center">M</td>
<td>变长字符串，要求M&lt;=255</td>
</tr>
<tr>
<td align="center">Binary(M)</td>
<td align="center">M</td>
<td>类似Char的二进制存储，特点是插入定长不足补0</td>
</tr>
<tr>
<td align="center">VarBinary(M)</td>
<td align="center">M</td>
<td>类似VarChar的变长二进制存储，特点是定长不补0</td>
</tr>
<tr>
<td align="center">Tiny Text</td>
<td align="center">Max:255</td>
<td>大小写不敏感</td>
</tr>
<tr>
<td align="center">Text</td>
<td align="center">Max:64K</td>
<td>大小写不敏感</td>
</tr>
<tr>
<td align="center">Medium Text</td>
<td align="center">Max:16M</td>
<td>大小写不敏感</td>
</tr>
<tr>
<td align="center">Long Text</td>
<td align="center">Max:4G</td>
<td>大小写不敏感</td>
</tr>
<tr>
<td align="center">TinyBlob</td>
<td align="center">Max:255</td>
<td>大小写敏感</td>
</tr>
<tr>
<td align="center">Blob</td>
<td align="center">Max:64K</td>
<td>大小写敏感</td>
</tr>
<tr>
<td align="center">MediumBlob</td>
<td align="center">Max:16M</td>
<td>大小写敏感</td>
</tr>
<tr>
<td align="center">LongBlob</td>
<td align="center">Max:4G</td>
<td>大小写敏感</td>
</tr>
<tr>
<td align="center">Enum</td>
<td align="center">1或2</td>
<td>最大可达65535个不同的枚举值</td>
</tr>
<tr>
<td align="center">Set</td>
<td align="center">可达8</td>
<td>最大可达64个不同的值</td>
</tr>
</tbody></table>
<h3 id="增删改查操作"><a href="#增删改查操作" class="headerlink" title="增删改查操作"></a>增删改查操作</h3><ul>
<li><p><strong>数据库命令以英文的分号结尾</strong></p>
</li>
<li><p><strong>sql对大小写不敏感</strong></p>
</li>
<li><p>库操作</p>
</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 创建数据库</span><br><span class="line">create database news;</span><br><span class="line"># 显示已有数据库</span><br><span class="line">show databases;</span><br><span class="line"># 选用数据库</span><br><span class="line">use news;</span><br><span class="line"># 删除数据库</span><br><span class="line">drop news;</span><br></pre></td></tr></table></figure>

<ul>
<li>表创建<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"># 选用数据库后，创建表</span><br><span class="line">use databases;</span><br><span class="line">ceate table news_table(</span><br><span class="line">    id int primary key,</span><br><span class="line">    title varchar(100) NOt NULL,</span><br><span class="line">    category varchar(10) NOT NULL,</span><br><span class="line">    content text,</span><br><span class="line">    tags tinyint</span><br><span class="line">);</span><br><span class="line"># 显示表结构</span><br><span class="line">desc news_table;</span><br><span class="line"># 查看表建立语句</span><br><span class="line">show create table news_table;</span><br><span class="line"># 删除表</span><br><span class="line">drop news_table;</span><br><span class="line"># 清空表数据，保留表结构</span><br><span class="line">truncate news_table;</span><br><span class="line"># 添加唯一约束</span><br><span class="line">alter table news_table add unique(title);</span><br><span class="line"># 删除唯一约束</span><br><span class="line">alter table news_table drop index title;</span><br><span class="line"># 设置主键自增</span><br><span class="line">alter table news_tale change id id auto_incrament;</span><br><span class="line"># 设置缺省值</span><br><span class="line">alter table news_table alter column tag set default 0;</span><br><span class="line"># 复合主键</span><br><span class="line">CREATE TABLE `w_test` (</span><br><span class="line">  `id` int(11) NOT NULL AUTO_INCREMENT,</span><br><span class="line">  `name` varchar(255) NOT NULL,</span><br><span class="line">  PRIMARY KEY (`id`,`name`)</span><br><span class="line">) ENGINE=InnoDB DEFAULT CHARSET=utf8;</span><br></pre></td></tr></table></figure></li>
</ul>
<h4 id="表-增-insert"><a href="#表-增-insert" class="headerlink" title="表/增(insert)"></a>表/增(insert)</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 1. 不指定插入数据的列名，直接插入值</span><br><span class="line">insert into news_table values (&#x27;title1&#x27;,&#x27;category1&#x27;,&#x27;content1&#x27;,1);</span><br><span class="line"># 2. 指定列名插入值</span><br><span class="line">insert into news_table (title,category,content) values(&#x27;title2&#x27;,&#x27;category2&#x27;,&#x27;content2&#x27;);</span><br><span class="line">3. 批量插入值</span><br><span class="line">insert into news_table values (&#x27;title1&#x27;,&#x27;category1&#x27;,&#x27;content1&#x27;,1),</span><br><span class="line">(&#x27;title2&#x27;,&#x27;category1&#x27;,&#x27;content1&#x27;,1),</span><br><span class="line">(&#x27;title3&#x27;,&#x27;category1&#x27;,&#x27;content1&#x27;,1),</span><br><span class="line">(&#x27;title4&#x27;,&#x27;category1&#x27;,&#x27;content1&#x27;,1);</span><br></pre></td></tr></table></figure>

<h4 id="表-查-select"><a href="#表-查-select" class="headerlink" title="表/查(select)"></a>表/查(select)</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"># 基本语法</span><br><span class="line">select column_name,column_name from table_name [where clause] [limit n][offset M]</span><br><span class="line"># 查询表中所有列</span><br><span class="line">select * from news_table;</span><br><span class="line"># 查询表中指定列</span><br><span class="line">select news_table.title,content,tag form news_table;</span><br><span class="line"># 指定列别名</span><br><span class="line">select title as T,content as C from news_table;</span><br><span class="line"># 对字段做四则运算</span><br><span class="line">select tag+10 form news_table;</span><br><span class="line"># 显示表中前3行</span><br><span class="line">select top 3 *from news_table;</span><br><span class="line"># 显示表中前30%的行数据</span><br><span class="line">select 30 percent *from news_table;</span><br><span class="line"># 消除指定列内重复数据</span><br><span class="line">select distinct title from news_table;</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"># 排序，asc为升序（默认），desc为降序</span><br><span class="line">select *from news_table order by tag asc,title desc;</span><br><span class="line"># 条件查询中可以使用的运算符</span><br><span class="line"># 比较运算符         &lt;=，&lt;，=，&gt;，&gt;=，!=，&lt;&gt;，!&gt;，!&lt;</span><br><span class="line"># 范围运算符         between... and，not between... and</span><br><span class="line"># 列举运算符         in，not in</span><br><span class="line"># 模糊匹配运算符     like，not like</span><br><span class="line"># 空值运算符         is null，is not null</span><br><span class="line"># 逻辑运算符         and，or，not</span><br><span class="line"></span><br><span class="line"># 取表中id值在10到20的数据</span><br><span class="line">select *from news_table where id&gt;=10 and id&lt;=20;</span><br><span class="line">select *from news_table where id between 10 and 20;</span><br><span class="line"># 显示表中id值为3,12,21的数据</span><br><span class="line">select *from news_table where id in (3,12,21);</span><br><span class="line">select *from news_table where id=3 or id=12 or id=21;</span><br><span class="line"># 显示表中id值不为3,12,21的数据//逻辑运算符：and, or,not...</span><br><span class="line">select *from news_table where id not in (3,12,21);</span><br></pre></td></tr></table></figure>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"># like模糊查询，%任意位，_一位，^取反</span><br><span class="line"># 查询某字段中以1结尾的数据</span><br><span class="line">select *from news_table where title like &#x27;%1&#x27;;</span><br><span class="line"># 查询id字段值第二个字符为2的数据</span><br><span class="line">select *from news_table where id like &#x27;_2%&#x27;;</span><br><span class="line"># 查询id字段值不在10-20之间的数据</span><br><span class="line">select *from news_table where id like[^10-20];</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"># join联合查询.inter join..on..内连接，将两表连接查询，显示交集//left join..on..左外连接，匹配两表数据显示左表数据//right join..on..右外连接，类左外//full join..on..全连接，保留两表数据（mysql不支持全链接）</span><br><span class="line">seletc *from table_a a inner join table_b b on a.id=b.id;# 取a,b交集</span><br><span class="line">seletc *from table_a a left join table_b b on a.id=b.id;# 取a表</span><br><span class="line">seletc *from table_a a right join table_b b on a.id=b.id;# 取b表</span><br><span class="line">seletc *from table_a a left join table_b b on a.id=b.id where b.id is null;# 取a表,且去除b表数据；仅在a表</span><br><span class="line"># ---实现全连接---利用union查询合并实现全链接</span><br><span class="line"># 取a,b表所有数据</span><br><span class="line">seletc *from table_a as a left join table_b b on a.id=b.id</span><br><span class="line">union</span><br><span class="line">seletc *from table_a a right join table_b b on a.id=b.id;</span><br><span class="line"># 取a,b表无交集外的所有数据</span><br><span class="line">seletc *from table_a a left join table_b b on a.id=b.id where b.id is null</span><br><span class="line">union</span><br><span class="line">seletc *from table_a a right join table_b b on a.id=b.id where a.id is null;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"># limit基本语法</span><br><span class="line">select *from table limit [offset,] rows|rows offset offsetv;</span><br><span class="line"># 显示查找记录11-25</span><br><span class="line">select *from news_table limit 11,25;</span><br><span class="line">select *from news_table limit 15 offset 10;</span><br><span class="line"># 显示查找记录11以后数据,指定第二个参数为-1</span><br><span class="line">select *from news_table limit 11,-1;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"># 单行处理函数：一个输入对应一个输出</span><br><span class="line"># lower 转换小写</span><br><span class="line"> select lower(ename) as ename from emp;</span><br><span class="line"># upper 转换大写</span><br><span class="line">select upper(name) as name from t_student;</span><br><span class="line"># substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）注意：起始下标从1开始，没有0.</span><br><span class="line">select substr(ename, 1, 1) as ename from emp;</span><br><span class="line"># concat函数进行字符串的拼接</span><br><span class="line">select concat(empno,ename) from emp;</span><br><span class="line"># length 取长度</span><br><span class="line">select length(ename) enamelength from emp;</span><br><span class="line"># trim 去空格</span><br><span class="line">select * from emp where ename = trim(&#x27;   KING&#x27;);</span><br><span class="line"># str_to_date 将字符串转换成日期</span><br><span class="line"># date_format 格式化日期</span><br><span class="line"># format 设置千分位</span><br><span class="line"># round 四舍五入</span><br><span class="line">select round(1236.567, 0) as result from emp; //保留整数位。</span><br><span class="line"># rand() 生成随机数</span><br><span class="line">select round(rand()*100,0) from emp; // 100以内的随机数</span><br><span class="line"># ifnull 可以将 null 转换成一个具体值。ifnull是空处理函数。专门处理空的。在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。</span><br><span class="line">select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 分组函数。分组函数不能写在where之后（where执行时，数据还未分组）</span><br><span class="line"># 统计数据条数count</span><br><span class="line">select count(*) as dn from news_table where tag=1;</span><br><span class="line"># 取列最大值max//min为最小值</span><br><span class="line">select max(id) as mxid from news_table;</span><br><span class="line"># 获取查询行的id平均值avf//sum为求和</span><br><span class="line">select avg(id) as avgid from news_table;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"># group by语句根据一个或多个列对结果进行分组。在分组的列上可以使用count，sum等函数</span><br><span class="line">select ... from ... where ... group by ...</span><br><span class="line">SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;</span><br><span class="line">+--------+----------+</span><br><span class="line">| name   | COUNT(*) |</span><br><span class="line">+--------+----------+</span><br><span class="line">| 小丽 |        1 |</span><br><span class="line">| 小明 |        3 |</span><br><span class="line">| 小王 |        2 |</span><br><span class="line">+--------+----------+</span><br><span class="line">3 rows in set (0.01 sec)</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"># where与having</span><br><span class="line"># 1. where和having都可以使用的场景</span><br><span class="line">select goods_price,goods_name from sw_goods where goods_price &gt; 100</span><br><span class="line">select goods_price,goods_name from sw_goods having goods_price &gt; 100</span><br><span class="line"># 2. 只可以用where，不可以用having的情况</span><br><span class="line">select goods_name,goods_number from sw_goods where goods_price &gt; 100</span><br><span class="line">select goods_name,goods_number from sw_goods having goods_price &gt; 100 # 报错！！！因为前面并没有筛选出goods_price 字段</span><br><span class="line"># 只可以用having，不可以用where情况</span><br><span class="line">select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag &gt; 1000</span><br><span class="line">select goods_category_id , avg(goods_price) as ag from sw_goods where ag&gt;1000 group by goods_category # 报错！！因为from sw_goods 这张数据表里面没有ag这个字段</span><br><span class="line"></span><br><span class="line"># 利用having过滤分组查询后的数据</span><br></pre></td></tr></table></figure>

<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 子查询：select语句中嵌套select语句，被嵌套的select语句称为子查询</span><br><span class="line">select ...(select)... from ... (select) ... where ... (select)</span><br><span class="line">select title from news_table where id&gt; (select count(tag) from news_table);</span><br><span class="line">select text from (select *from news_table where tag&gt;0);</span><br></pre></td></tr></table></figure>



<p> <strong>union与union all</strong></p>
<blockquote>
<ul>
<li><p>UNION 的语法如下：</p>
<pre><code> [SQL 语句 1]
  UNION
 [SQL 语句 2]
</code></pre>
</li>
<li><p>UNION ALL 的语法如下：</p>
<pre><code> [SQL 语句 1]
  UNION ALL
 [SQL 语句 2]
</code></pre>
</li>
</ul>
<hr>
<ul>
<li><p>对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。</p>
</li>
<li><p>对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。</p>
</li>
</ul>
<p>从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。</p>
</blockquote>
<h4 id="表-删-delete"><a href="#表-删-delete" class="headerlink" title="表/删(delete)"></a>表/删(delete)</h4><figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 删除指定行</span><br><span class="line">delete from news_table where id=3;</span><br><span class="line"># 删除所有行</span><br><span class="line">delete *from news_table; // delete from nes_table;</span><br></pre></td></tr></table></figure>

<h4 id="表-改-update"><a href="#表-改-update" class="headerlink" title="表/改(update)"></a>表/改(update)</h4><p><strong>Update 语句用于修改表中的数据</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">update news_table set title=&#x27;update&#x27; where id=1;</span><br></pre></td></tr></table></figure>
<h3 id="视图"><a href="#视图" class="headerlink" title="视图"></a>视图</h3><p>视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。 </p>
<p><strong>为什么要使用视图？</strong></p>
<blockquote>
<p>1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。<br>2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。<br>3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。<br>总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。 </p>
</blockquote>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 创建视图</span><br><span class="line">create view vnews as select *from news_table [with check option];# []中为可选项，作用是保证数据的安全性</span><br><span class="line"># 查看视图</span><br><span class="line">show create view;</span><br><span class="line"># 修改视图</span><br><span class="line">alter view vnews as select *from news_table where tag=1 [with check option];</span><br><span class="line"># 删除视图</span><br><span class="line">drop view [if exists] vnews [, view_name];</span><br></pre></td></tr></table></figure>

<h3 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h3><p>索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。<br>一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。</p>
<p>在mysql数据库当中索引也是需要排序的，并且这个索引的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。<br>遵循左小右大原则存放。采用中序遍历方式遍历取数据。</p>
<ul>
<li>提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。因此，通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。</li>
<li>提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。</li>
<li>提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）</li>
</ul>
<p><strong>索引失效</strong></p>
<p>使用<code>explain [查询语句]</code>可查看是否使用了索引</p>
<ul>
<li><code>select * from emp where ename like &#39;%T&#39;;</code>原因是因为模糊匹配当中以“%”开头了！<strong>尽量避免模糊查询的时候以“%”开始</strong>。这是一种优化的手段/策略。</li>
<li>使用or的时候会失效，如果<strong>使用or那么要求or两边的条件字段都要有索引</strong>，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。</li>
<li>使用复合索引的时候，没有使用左侧的列查找，索引失效</li>
<li>在where当中索引列参加了运算，索引失效。</li>
<li>在where当中索引列使用了函数</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 创建索引：</span><br><span class="line">create index emp_ename_index on emp(ename); # 给emp表的ename字段添加索引，起名：emp_ename_index</span><br><span class="line"># 删除索引：</span><br><span class="line">drop index emp_ename_index on emp; # 将emp表上的emp_ename_index索引对象删除。</span><br></pre></td></tr></table></figure>

<p><strong>什么条件下，我们会考虑给字段添加索引呢</strong></p>
<ul>
<li>条件1：数据量庞大</li>
<li>条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。</li>
<li>条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）</li>
</ul>
<h3 id="存储引擎"><a href="#存储引擎" class="headerlink" title="存储引擎"></a>存储引擎</h3><ul>
<li><p>什么是存储引擎，有什么用呢？</p>
<blockquote>
<pre><code>存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）实际上存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。mysql默认的存储引擎是：InnoDB；mysql默认的字符编码方式是：utf8
</code></pre>
</blockquote>
</li>
<li><p>可以在建表的时候给表指定存储引擎。</p>
</li>
</ul>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLE `t_student` (</span><br><span class="line">	  `no` int(11) NOT NULL AUTO_INCREMENT,</span><br><span class="line">	  `name` varchar(255) DEFAULT NULL,</span><br><span class="line">	  `cno` int(11) DEFAULT NULL,</span><br><span class="line">	  PRIMARY KEY (`no`),</span><br><span class="line">	  KEY `cno` (`cno`),</span><br><span class="line">	  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)</span><br><span class="line">	) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8</span><br></pre></td></tr></table></figure>

<ul>
<li><p>MyISAM存储引擎</p>
<blockquote>
<p><strong>它管理的表具有以下特征：</strong></p>
<pre><code>    使用三个文件表示每个表：
        格式文件 — 存储表结构的定义（mytable.frm）
        数据文件 — 存储表行的内容（mytable.MYD）
        索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
</code></pre>
<hr>
<p>对于一张表来说，只要是主键，或者加有unique约束的字段上会自动创建索引。<br>MyISAM存储引擎特点：</p>
<pre><code>可被转换为压缩、只读表来节省空间
</code></pre>
</blockquote>
</li>
<li><p>InnoDB存储引擎</p>
<blockquote>
<p>这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。</p>
<pre><code>InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。
</code></pre>
<hr>
<p>它管理的表具有下列主要特征：</p>
<ul>
<li>每个 InnoDB 表在数据库目录中以.frm 格式文件表示</li>
<li>InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）</li>
<li> 提供一组用来记录事务性活动的日志文件</li>
<li> 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理</li>
<li>提供全 ACID 兼容</li>
<li>在 MySQL 服务器崩溃后提供自动恢复</li>
<li>多版本（MVCC）和行级锁定</li>
<li> 支持外键及引用的完整性，包括级联删除和更新</li>
</ul>
<hr>
<p><strong>InnoDB最大的特点就是支持事务：</strong></p>
<ul>
<li>以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读</li>
<li>不能很好的节省存储空间</li>
</ul>
</blockquote>
</li>
<li><p>MEMORY存储引擎</p>
<blockquote>
<p>使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。</p>
<p><strong>MEMORY 存储引擎管理的表具有下列特征：</strong></p>
<ul>
<li>在数据库目录内，每个表均以.frm 格式的文件表示。</li>
<li>表数据及索引被存储在内存中。（目的就是快，查询快！）</li>
<li>表级锁机制。</li>
<li>不能包含 TEXT 或 BLOB 字段。</li>
</ul>
<p><strong>MEMORY引擎优点:</strong></p>
<ul>
<li>查询效率是最高的。不需要和硬盘交互。</li>
<li>不安全，关机之后数据消失。因为数据和索引都是在内存当中。</li>
</ul>
</blockquote>
</li>
</ul>
<h3 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h3><p>一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元。不可再分。本质是批量的DML语句同时成功，或者同时失败！</p>
<blockquote>
<p>什么是一个完整的业务逻辑？<br>        假设转账，从A账户向B账户中转账10000.<br>        将A账户的钱减去10000（update语句）<br>        将B账户的钱加上10000（update语句）<br>        这就是一个完整的业务逻辑。<br>以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。</p>
</blockquote>
<ul>
<li><p>事务的4个特性</p>
<blockquote>
<ol>
<li><strong>原子性</strong> | 说明事务是最小的工作单元。不可再分。</li>
<li><strong>一致性</strong> | 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。</li>
<li><strong>隔离性</strong> | A事务和B事务之间具有一定的隔离。</li>
<li><strong>持久性</strong> | 事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！</li>
</ol>
</blockquote>
</li>
<li><p>事务是怎么做到多条DML语句同时成功和同时失败的</p>
<blockquote>
<p><strong>InnoDB存储引擎：提供一组用来记录事务性活动的日志文件</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">事务开启了：</span><br><span class="line">	insert</span><br><span class="line">	insert</span><br><span class="line">	insert</span><br><span class="line">	delete</span><br><span class="line">	update</span><br><span class="line">	update</span><br><span class="line">	update</span><br><span class="line">事务结束了！</span><br></pre></td></tr></table></figure>

<p>在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。在事务的执行过程中，我们可以提交事务，也可以回滚事务。</p>
<p><strong>提交事务</strong></p>
<p>清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。<br>提交事务标志着，事务的结束。并且是一种全部<strong>成功的结束</strong>。</p>
<p><strong>回滚事务</strong></p>
<p>将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件<br>回滚事务标志着，事务的结束。并且是一种全部<strong>失败的结束</strong>。</p>
</blockquote>
</li>
<li><p>怎么提交事务，怎么回滚事务</p>
<blockquote>
<p>提交事务：commit; 语句<br>回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）<br>事务：transaction</p>
<hr>
<p><strong>mysql默认情况下是支持自动提交事务的。</strong>（自动提交：每执行一条DML语句，则提交一次！）</p>
<p><strong>关闭自动提交机制：start transaction;</strong></p>
</blockquote>
</li>
</ul>
<h3 id="数据库设计三范式"><a href="#数据库设计三范式" class="headerlink" title="数据库设计三范式"></a>数据库设计三范式</h3><p>设计数据库表的时候，按照三范式进行，可以避免表中数据的冗余，空间的浪费。</p>
<p>实践和理论有的时候有偏差。</p>
<figure class="highlight txt"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。</span><br></pre></td></tr></table></figure>
<ul>
<li>第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。</li>
<li>第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，<pre><code>不要产生部分依赖。
</code></pre>
</li>
<li>第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，<pre><code>不要产生传递依赖。
</code></pre>
</li>
</ul>
<p>表设计总结：</p>
<ul>
<li>一对多：一对多，两张表，多的表加外键</li>
<li>多对多：多对多，三张表，关系表两个外键</li>
<li>一对一：单表</li>
</ul>
<h3 id="其他"><a href="#其他" class="headerlink" title="其他"></a>其他</h3><ul>
<li><code>set statistics time on;</code>打开消息栏显示详细执行时间，off为关闭</li>
<li><code>format(name,&#39;$n&#39;)</code>格式化数据显示方式</li>
<li><code>str_to_date(&#39;字符串日期&#39;,&#39;日期格式&#39;)</code>将字符串转换成<code>date</code>对应的日期类型。<code>str_to_date(&#39;01-10-2021&#39;,&#39;%d-%m-%y&#39;)</code>;在mysql中若字符串格式为’%y-%m-%d’(‘2021-10-01’)，则可以直接作插入操作，会进行自动格式转换</li>
<li><code>date_format(&#39;日期类型数据&#39;,&#39;日期格式&#39;)</code>；在查询显示时，若不使用日期格式话，则显示为默认格式<code>%y-%m-%d(2021-10-01)</code></li>
<li><code>date</code>：段日期，包括年月日；<code>datetime</code>：长日期，包括年月日时分秒</li>
<li><code>show engines \G;</code>查看mysql支持的所有存储引擎的类型</li>
<li>数据导出：<code>mysqldump [数据库名] [存储路径] -uroot -p...</code> –&gt;在命令行执行即可，不需登录mysql数据库。</li>
<li>数据导入：1.登录mysql数据库服务；2.创建数据库；3.进入该数据库；4.加载数据：<code>source [文件路径]</code></li>
</ul>
<h3 id="参考链接"><a href="#参考链接" class="headerlink" title="参考链接"></a>参考链接</h3><font size=2>

<p>[1] <a target="_blank" rel="noopener" href="https://blog.csdn.net/qq_38328378/article/details/80858073">MySQL基础 — 常用命令</a></p>
<p>[2] <a target="_blank" rel="noopener" href="https://www.cnblogs.com/-xlp/p/8617760.html">MYSQL中数据类型介绍</a></p>
<p>[3] <a target="_blank" rel="noopener" href="https://www.runoob.com/mysql/mysql-tutorial.html">MySQL 教程</a></p>
<p>[4] <a target="_blank" rel="noopener" href="https://www.bilibili.com/video/BV1Vy4y1z7EX?from=search&seid=20469657660367333&spm_id_from=333.337.0.0">老杜带你学_mysql入门基础</a></p>
<p></font>&gt;</p>

    </div>

    
    
    

      <footer class="post-footer">
          
          <div class="post-tags">
              <a href="/blog/tags/mysql/" rel="tag"><i class="fa fa-tag"></i> mysql</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/blog/2021/09/05/shell/" rel="prev" title="shell">
      <i class="fa fa-chevron-left"></i> shell
    </a></div>
      <div class="post-nav-item">
    <a href="/blog/2021/10/21/%E8%AE%A1%E7%BD%91/" rel="next" title="计网">
      计网 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          
    <div class="comments" id="valine-comments"></div>

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>


        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E5%9F%BA%E7%A1%80%E6%A6%82%E5%BF%B5"><span class="nav-number">1.</span> <span class="nav-text">数据库基础概念</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#DDL%E3%80%81DML%E5%92%8CDCL"><span class="nav-number">2.</span> <span class="nav-text">DDL、DML和DCL</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#mysql%E7%9A%84%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="nav-number">3.</span> <span class="nav-text">mysql的数据类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%A2%9E%E5%88%A0%E6%94%B9%E6%9F%A5%E6%93%8D%E4%BD%9C"><span class="nav-number">4.</span> <span class="nav-text">增删改查操作</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%A1%A8-%E5%A2%9E-insert"><span class="nav-number">4.1.</span> <span class="nav-text">表&#x2F;增(insert)</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%A1%A8-%E6%9F%A5-select"><span class="nav-number">4.2.</span> <span class="nav-text">表&#x2F;查(select)</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%A1%A8-%E5%88%A0-delete"><span class="nav-number">4.3.</span> <span class="nav-text">表&#x2F;删(delete)</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%A1%A8-%E6%94%B9-update"><span class="nav-number">4.4.</span> <span class="nav-text">表&#x2F;改(update)</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%A7%86%E5%9B%BE"><span class="nav-number">5.</span> <span class="nav-text">视图</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95"><span class="nav-number">6.</span> <span class="nav-text">索引</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E"><span class="nav-number">7.</span> <span class="nav-text">存储引擎</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%8B%E5%8A%A1"><span class="nav-number">8.</span> <span class="nav-text">事务</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1%E4%B8%89%E8%8C%83%E5%BC%8F"><span class="nav-number">9.</span> <span class="nav-text">数据库设计三范式</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%85%B6%E4%BB%96"><span class="nav-number">10.</span> <span class="nav-text">其他</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%8F%82%E8%80%83%E9%93%BE%E6%8E%A5"><span class="nav-number">11.</span> <span class="nav-text">参考链接</span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="bgape002"
      src="/blog/images/head.png">
  <p class="site-author-name" itemprop="name">bgape002</p>
  <div class="site-description" itemprop="description">mail: bgape002@163.com</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/blog/archives/">
        
          <span class="site-state-item-count">61</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/blog/categories/">
          
        <span class="site-state-item-count">27</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/blog/tags/">
          
        <span class="site-state-item-count">66</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 2021 – 
  <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">bgape002</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a> 强力驱动
  </div>

        
<div class="busuanzi-count">
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
    <span class="post-meta-item" id="busuanzi_container_site_uv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-user"></i>
      </span>
      <span class="site-uv" title="总访客量">
        <span id="busuanzi_value_site_uv"></span>
      </span>
    </span>
    <span class="post-meta-divider">|</span>
    <span class="post-meta-item" id="busuanzi_container_site_pv" style="display: none;">
      <span class="post-meta-item-icon">
        <i class="fa fa-eye"></i>
      </span>
      <span class="site-pv" title="总访问量">
        <span id="busuanzi_value_site_pv"></span>
      </span>
    </span>
</div>








      </div>
    </footer>
  </div>

  
  <script src="/blog/lib/anime.min.js"></script>
  <script src="/blog/lib/velocity/velocity.min.js"></script>
  <script src="/blog/lib/velocity/velocity.ui.min.js"></script>

<script src="/blog/js/utils.js"></script>

<script src="/blog/js/motion.js"></script>


<script src="/blog/js/schemes/pisces.js"></script>


<script src="/blog/js/next-boot.js"></script>




  




  
<script src="/blog/js/local-search.js"></script>











<script>
if (document.querySelectorAll('pre.mermaid').length) {
  NexT.utils.getScript('//cdn.jsdelivr.net/npm/mermaid@8/dist/mermaid.min.js', () => {
    mermaid.initialize({
      theme    : 'forest',
      logLevel : 3,
      flowchart: { curve     : 'linear' },
      gantt    : { axisFormat: '%m/%d/%Y' },
      sequence : { actorMargin: 50 }
    });
  }, window.mermaid);
}
</script>


  

  

  


<script>
NexT.utils.loadComments(document.querySelector('#valine-comments'), () => {
  NexT.utils.getScript('//unpkg.com/valine/dist/Valine.min.js', () => {
    var GUEST = ['nick', 'mail', 'link'];
    var guest = 'nick,mail';
    guest = guest.split(',').filter(item => {
      return GUEST.includes(item);
    });
    new Valine({
      el         : '#valine-comments',
      verify     : false,
      notify     : false,
      appId      : 'szhBf0Qamzsowubi1WnkXmUj-gzGzoHsz',
      appKey     : 'WpwxoK0fVJHvhzWwakr9vbpA',
      placeholder: "Just go go",
      avatar     : 'mm',
      meta       : guest,
      pageSize   : '10' || 10,
      visitor    : false,
      lang       : '' || 'zh-cn',
      path       : location.pathname,
      recordIP   : false,
      serverURLs : ''
    });
  }, window.Valine);
});
</script>

</body>
</html>
